package com.zglc.ms.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.zglc.ms.shop.model.ShopProduct;
import com.zglc.ms.shop.model.ShopProductVo;

@Repository
public class ProductDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int insertShopProduct(ShopProduct shopProduct) {
		final String sql = "INSERT INTO shop_product( name, label, type, fictitious_type, ticket_system_id, "
				+ " weight, img_big_url, img_small_url, price_market, unit, product_num, inventory_num, "
				+ " show_saled_num, real_saled_num,  content, is_up, is_send,  created_at, "
				+ " updated_at,  content_type) VALUES (?, ?, ?, ?, ?,   ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?,  ?, ?, NOW(), "
				+ " NOW(), ?)";
		System.out.println("插入产品sql： " + sql);
		System.out.println("插入参数： " + shopProduct.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopProduct.getName());
					ps.setString(2, shopProduct.getLabel());
					ps.setLong(3, shopProduct.getType());
					ps.setLong(4, shopProduct.getFictitiousType());
					ps.setLong(5, shopProduct.getTicketSystemId());

					ps.setInt(6, shopProduct.getWeight());
					ps.setString(7, shopProduct.getImgBigUrl());
					ps.setString(8, shopProduct.getImgSmallUrl());
					ps.setLong(9, shopProduct.getPriceMarket());
					ps.setString(10, shopProduct.getUnit());
					ps.setLong(11, shopProduct.getProductNum());
					ps.setInt(12, shopProduct.getInventoryNum());

					ps.setInt(13, shopProduct.getShowSaledNum());
					ps.setInt(14, shopProduct.getRealSaledNum());
					ps.setString(15, shopProduct.getContent());
					ps.setInt(16, shopProduct.getIsUp());
					ps.setInt(17, shopProduct.getIsSend());
					ps.setInt(18, shopProduct.getContentType());
					return ps;
				}
			}, keyHolder);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}

	public int UpdateShopProduct(ShopProduct shopProduct) {
		final String sql = " UPDATE shop_product SET name = ?, label = ?, type = ?,  fictitious_type = ?,  ticket_system_id = ?,  "
				+ " weight = ?,  img_big_url = ?,  img_small_url = ?, price_market = ?,  unit = ?,  product_num = ?,  inventory_num = ?,  "
				+ " show_saled_num = ?, real_saled_num = ?, content = ?,  is_up = ?,  is_send = ?, "
				+ " updated_at = NOW(), content_type = ? WHERE id = ? ";
		System.out.println("更新产品sql： " + sql);
		System.out.println("更新参数： " + shopProduct.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			jdbcTemplate.update(new PreparedStatementCreator() {

				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopProduct.getName());
					ps.setString(2, shopProduct.getLabel());
					ps.setLong(3, shopProduct.getType());
					ps.setLong(4, shopProduct.getFictitiousType());
					ps.setLong(5, shopProduct.getTicketSystemId());

					ps.setInt(6, shopProduct.getWeight());
					ps.setString(7, shopProduct.getImgBigUrl());
					ps.setString(8, shopProduct.getImgSmallUrl());
					ps.setLong(9, shopProduct.getPriceMarket());
					ps.setString(10, shopProduct.getUnit());
					ps.setLong(11, shopProduct.getProductNum());
					ps.setInt(12, shopProduct.getInventoryNum());

					ps.setInt(13, shopProduct.getShowSaledNum());
					ps.setInt(14, shopProduct.getRealSaledNum());
					ps.setString(15, shopProduct.getContent());
					ps.setInt(16, shopProduct.getIsUp());
					ps.setInt(17, shopProduct.getIsSend());
					ps.setInt(18, shopProduct.getContentType());
					ps.setInt(19, shopProduct.getId());
					return ps;
				}
			}, keyHolder);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}

	public boolean updateShopStock(int id, int updateCnt) {
		final String sql = "UPDATE shop_product SET real_saled_num = real_saled_num + ?, "
				+ "show_saled_num = show_saled_num + ? WHERE id = ? AND inventory_num >= real_saled_num + ?";

		try {
			Integer updatedCnt = jdbcTemplate.update(sql, updateCnt, updateCnt, id, updateCnt);
			return updatedCnt != null;
		} catch (Exception e) {
		}

		return false;
	}
	
	public boolean updateShopStockNotLimitCount(int id, int updateCnt) {
		final String sql = "UPDATE shop_product SET real_saled_num = real_saled_num + ?, "
				+ "show_saled_num = show_saled_num + ? WHERE id = ?";// 不做太严格的限制

		try {
			Integer updatedCnt = jdbcTemplate.update(sql, updateCnt, updateCnt, id);
			return updatedCnt != null;
		} catch (Exception e) {
		}

		return false;
	}

	public ShopProduct getShopProductById(int productId) {
		final String sql = "SELECT * FROM shop_product  WHERE id = ? and is_up = true";

		try {
			ShopProduct info = jdbcTemplate.queryForObject(sql, new Object[] { productId },
					new RowMapper<ShopProduct>() {

						@Override
						public ShopProduct mapRow(ResultSet rs, int rowNum) throws SQLException {
							ShopProduct info = setShopProductForList(rs);
							return info;
						}
					});

			return info;
		} catch (DataAccessException e) {
			logger.info("getShopProductByCode e = " + e.getLocalizedMessage());
		}

		return null;
	}
	
	public List<ShopProduct> getShopProducts() {
		final String sql = "SELECT * FROM shop_product  WHERE is_up = true";

		ArrayList<Object> parmList = new ArrayList<Object>();
		try {
			List<ShopProduct> users = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]),
					new ResultSetExtractor<List<ShopProduct>>() {
						public List<ShopProduct> extractData(ResultSet rs) throws SQLException, DataAccessException {
							List<ShopProduct> list = new ArrayList<ShopProduct>();
							while (rs.next()) {
								ShopProduct info = setShopProductForList(rs);
								list.add(info);
							}
							return list;
						}
					});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopProducts by all = " + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public List<ShopProductVo> getShopProductVos() {
		final String sql = "SELECT * FROM shop_product  WHERE is_up = true";

		ArrayList<Object> parmList = new ArrayList<Object>();
		try {
			List<ShopProductVo> users = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]),
					new ResultSetExtractor<List<ShopProductVo>>() {
						public List<ShopProductVo> extractData(ResultSet rs) throws SQLException, DataAccessException {
							List<ShopProductVo> list = new ArrayList<ShopProductVo>();
							while (rs.next()) {
								ShopProductVo info = setShopProductVoForList(rs);
								list.add(info);
							}
							return list;
						}
					});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopProducts by all = " + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public ShopProduct getProductShowTransById(int productId) {
		final String sql = "SELECT * FROM shop_product  WHERE id = ?";

		try {
			ShopProduct info = jdbcTemplate.queryForObject(sql, new Object[] { productId },
					new RowMapper<ShopProduct>() {

						@Override
						public ShopProduct mapRow(ResultSet rs, int rowNum) throws SQLException {
							ShopProduct info = setShopProduct(rs);
							return info;
						}
					});

			return info;
		} catch (DataAccessException e) {
			logger.info("getShopProductByCode e = " + e.getLocalizedMessage());
		}

		return null;
	}
	
	private ShopProduct setShopProductForList(ResultSet rs) throws SQLException {
		ShopProduct info = new ShopProduct();
		info.setId(rs.getInt("id"));
		info.setName(rs.getString("name"));
		info.setLabel(rs.getString("label"));
		info.setType(rs.getInt("type"));
		info.setFictitiousType(rs.getInt("fictitious_type"));
		info.setTicketSystemId(rs.getInt("ticket_system_id"));
		info.setWeight(rs.getInt("weight"));
		info.setImgBigUrl(rs.getString("img_big_url"));
		info.setImgSmallUrl(rs.getString("img_small_url"));
		info.setPriceMarket(rs.getLong("price_market"));
		info.setUnit(rs.getString("unit"));
		info.setProductNum(rs.getLong("product_num"));
		info.setInventoryNum(rs.getInt("inventory_num"));
		info.setShowSaledNum(rs.getInt("show_saled_num"));
		info.setRealSaledNum(rs.getInt("real_saled_num"));
		info.setContent(rs.getString("content"));
		info.setIsUp(rs.getInt("is_up"));
		info.setIsSend(rs.getInt("is_send"));
		info.setUpDate(rs.getDate("up_date"));
		info.setCreatedAt(rs.getDate("created_at"));
		info.setUpdatedAt(rs.getDate("updated_at"));
		info.setContentType(rs.getInt("content_type"));
		return info;
	}
	
	private ShopProductVo setShopProductVoForList(ResultSet rs) throws SQLException {
		ShopProductVo info = new ShopProductVo();
		info.setId(rs.getInt("id"));
		info.setName(rs.getString("name"));
		info.setImgBigUrl(rs.getString("img_big_url"));
		info.setImgSmallUrl(rs.getString("img_small_url"));
		info.setInventoryNum(rs.getInt("inventory_num"));
		info.setRealSaledNum(rs.getInt("real_saled_num"));

		info.setPriceType(rs.getInt("price_type"));
		info.setPricePoint(rs.getLong("price_point"));
		info.setPriceCash(rs.getLong("price_cash"));
		return info;
	}
	

	private ShopProduct setShopProduct(ResultSet rs) throws SQLException {
		ShopProduct info = new ShopProduct();
		info.setId(rs.getInt("id"));
		info.setName(rs.getString("name"));
		info.setLabel(rs.getString("label"));
		info.setType(rs.getInt("type"));
		info.setFictitiousType(rs.getInt("fictitious_type"));
		info.setTicketSystemId(rs.getInt("ticket_system_id"));
		info.setWeight(rs.getInt("weight"));
		info.setImgBigUrl(rs.getString("img_big_url"));
		info.setImgSmallUrl(rs.getString("img_small_url"));
		info.setPriceMarket(rs.getLong("price_market"));
		info.setUnit(rs.getString("unit"));
		info.setProductNum(rs.getLong("product_num"));
		info.setInventoryNum(rs.getInt("inventory_num"));
		info.setShowSaledNum(rs.getInt("show_saled_num"));
		info.setRealSaledNum(rs.getInt("real_saled_num"));
		info.setContent(rs.getString("content"));
		info.setIsUp(rs.getInt("is_up"));
		info.setIsSend(rs.getInt("is_send"));
		info.setUpDate(rs.getDate("up_date"));
		info.setCreatedAt(rs.getDate("created_at"));
		info.setUpdatedAt(rs.getDate("updated_at"));
		info.setContentType(rs.getInt("content_type"));
		info.setPriceType(rs.getInt("price_type"));
		info.setPricePoint(rs.getLong("price_point"));
		info.setPriceCash(rs.getLong("price_cash"));
		return info;
	}

	public List<ShopProduct> getShopProductByAll(String name, int isSend, String label, String content, int page) {

		StringBuffer sql = new StringBuffer("SELECT  * FROM shop_product WHERE 1=1 and is_up = true ");
		ArrayList<Object> parmList = new ArrayList<Object>();

		if (!StringUtils.isEmpty(name)) {
			sql.append(" AND name like  ");
			sql.append(" '%" + name + "%' ");
		}

		if (isSend >= 0) {
			sql.append(" AND  is_send =  ");
			sql.append(isSend);
		}

		if (!StringUtils.isEmpty(label)) {
			sql.append(" AND label like  ");
			sql.append(" '%" + label + "%' ");
		}

		if (!StringUtils.isEmpty(content)) {
			sql.append(" AND ( name LIKE ");
			sql.append("'%" + name + "%' OR ");
			sql.append("  is_send = '");
			sql.append(isSend + "'  OR label LIKE ");
			sql.append("'%" + label + "%'  ) ");
		}

		if (page > 0) {
			sql.append(" ORDER BY id DESC LIMIT ").append(10 * (page - 1)).append(",").append(10);
		} else {
			sql.append(" ORDER BY id DESC");
		}
		System.out.println("查询产品sql： " + sql);
		System.out.println("查询参数： " + name + " | " + isSend + " | " + label + " | " + content + " | " + page);

		try {
			List<ShopProduct> users = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]),
					new ResultSetExtractor<List<ShopProduct>>() {
						public List<ShopProduct> extractData(ResultSet rs) throws SQLException, DataAccessException {
							List<ShopProduct> list = new ArrayList<ShopProduct>();
							while (rs.next()) {
								ShopProduct info = setShopProduct(rs);
								list.add(info);
							}
							return list;
						}
					});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist ShopProduct by all = " + "|" + e.getLocalizedMessage());
			return null;
		}
	}

	public Integer getShopProductCnt(String name, int isSend, String label, String content, int page) {

		StringBuffer sql = new StringBuffer("SELECT  count(1) cnt FROM shop_product WHERE 1=1 and is_up = true ");
		ArrayList<Object> parmList = new ArrayList<Object>();

		if (!StringUtils.isEmpty(name)) {
			sql.append(" AND name like  ");
			sql.append(" '%" + name + "%' ");
		}

		if (isSend >= 0) {
			sql.append(" AND is_send =  ");
			sql.append(isSend);
		}

		if (!StringUtils.isEmpty(label)) {
			sql.append(" AND label like  ");
			sql.append(" '%" + label + "%' ");
		}

		if (!StringUtils.isEmpty(content)) {
			sql.append(" AND ( name LIKE ");
			sql.append(" '%" + name + "%' OR ");
			sql.append("  is_send = '");
			sql.append(isSend + "'  OR label LIKE ");
			sql.append(" '%" + label + "%'  ) ");
		}
		System.out.println("查询产品sql： " + sql);
		System.out.println("查询参数： " + name + " | " + isSend + " | " + label + " | " + content + " | " + page);
		Integer count = jdbcTemplate.query(sql.toString(), parmList.toArray(new Object[parmList.size()]), (rs) -> {
			Integer i = null;
			while (rs.next()) {
				i = rs.getInt("cnt");
			}
			return i;
		});
		return count;
	}

	public Map<Integer, ProductSimpleInfo> getIdNameMap() {
		final String sql = "SELECT id, name, img_small_url, price_type FROM shop_product";
		Map<Integer, ProductSimpleInfo> map = new HashMap<>();
		jdbcTemplate.query(sql, (rs, rowNum) -> {
			map.put(rs.getInt("id"), new ProductSimpleInfo(rs.getString("name"), 
					rs.getString("img_small_url"), rs.getInt("price_type")));
			return null;
		});

		return map;
	}

	public static class ProductSimpleInfo {
		private String name;
		private String imgSmallUrl;
		private int priceType;
		
		public ProductSimpleInfo(String name, String imgSmallUrl, int priceType) {
			this.name = name;
			this.imgSmallUrl = imgSmallUrl;
			this.priceType = priceType;
		}

		public String getName() {
			return name;
		}

		public String getImgSmallUrl() {
			return imgSmallUrl;
		}

		public int getPriceType() {
			return priceType;
		}
		
	}
}